/*
 *  Copyright (c) 2009, Hugo Augusto Alves
 *  All rights reserved.
 *
 *  Redistribution and use in source and binary forms, with or without
 *  modification, are permitted provided that the following conditions are met:
 *      * Redistributions of source code must retain the above copyright
 *        notice, this list of conditions and the following disclaimer.
 *      * Redistributions in binary form must reproduce the above copyright
 *        notice, this list of conditions and the following disclaimer in the
 *        documentation and/or other materials provided with the distribution.
 *      * Neither the name of the Hugo Augusto Alves nor the
 *        names of its contributors may be used to endorse or promote products
 *        derived from this software without specific prior written permission.
 *
 *  THIS SOFTWARE IS PROVIDED BY COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY
 *  EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 *  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 *  DISCLAIMED. IN NO EVENT SHALL COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY
 *  DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 *  (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 *  LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 *  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 *  (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 *  SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package org.wiscriptor.db;

import org.wiscriptor.wiki.Page;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import org.wiscriptor.internalization.I18N;
import org.wiscriptor.wiki.Category;
import org.wiscriptor.wiki.Version;

/**
 *
 * @author Hugo
 */
// TODO: se categoria for null - categoria padrao - nome da wiki
public class WikiDataBase {

    private String dbname = "";

    public WikiDataBase(String dbname) {
        this.dbname = dbname;
        try {
            // load the sqlite-JDBC driver using the current class loader
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
        }
        createTables();
    }

    private void createTables() {
        Connection connection = null;
        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbname);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            // page table
            statement.executeUpdate("CREATE TABLE IF NOT EXISTS page (" +
                    "page_name TEXT PRIMARY KEY, " +
                    "page_text TEXT, " +
                    "page_version TEXT," +
                    "page_comment TEXT," +
                    "cat_name TEXT)");
            // page_tag table
            statement.executeUpdate("CREATE TABLE IF NOT EXISTS page_tag (" +
                    "pt_page TEXT, " +
                    "pt_tag TEXT)");
            // version table
            statement.executeUpdate("CREATE TABLE IF NOT EXISTS version (" +
                    "ver_title TEXT, " +
                    "ver_quant INTEGER," +
                    "ver_date TEXT)");
            // category table
            statement.executeUpdate("CREATE TABLE IF NOT EXISTS category (" +
                    "cat_name TEXT PRIMARY KEY, " +
                    "cat_parent TEXT)");
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private void executeUpdate(String sql) {
        System.err.println("update: " + sql);
        Connection connection = null;
        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbname);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void insertOrUpdate(Page page) {
        Category cat = new Category(page.getCategory());
        if (getPage(page.getName()) == null) {
            insertPage(page.getName(), page.getText(), page.getVersion().getVersionString(), page.getComment(), cat.getName());
        } else {
            updatePage(page.getName(), page.getText(), page.getVersion().getVersionString(), page.getComment(), cat.getName());
        }
        if (getCategory(cat.getName()) == null) {
            executeUpdate("INSERT INTO category VALUES('" + cat.getName() + "', '" + cat.getParent() + "')");
        }
    }

    private void insertPage(String name, String text, String version, String comment, String category) {
        if (isPageNameValid(name) && isPageTextValid(text) && isPageVersionValid(version) && isPageCommentValid(comment) && isPageCategoryValid(category)) {
            executeUpdate("INSERT INTO page VALUES('" + name + "', '" + text + "', '" +
                    version + "', '" + comment + "', '" + category + "')");
        }
    }

    private void updatePage(String name, String text, String version, String comment, String category) {
        if (isPageNameValid(name) && isPageTextValid(text) && isPageVersionValid(version) && isPageCommentValid(comment) && isPageCategoryValid(category)) {
            executeUpdate("UPDATE page SET page_text = '" + text + "', page_version = '" + version +
                    "', page_comment = '" + new Date().toString() + "', cat_name = '" + category + "' WHERE page_name = '" + name + "'");
        }
    }

    public String getCategoryOfPage(String p) {
        Connection connection = null;
        String res = "";
        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbname);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
            String sql = "SELECT * FROM page WHERE page_name = '" + p + "'";
            System.err.println(sql);
            ResultSet rs = statement.executeQuery(sql);
            String cat = "";
            if (rs != null && rs.next()) {
                cat = rs.getString("cat_name");
            }
            if (!cat.equals("")) {
                sql = "SELECT * FROM category WHERE cat_name = '" + cat + "'";
                System.err.println(sql);
                rs = statement.executeQuery(sql);
                if (rs != null && rs.next()) {
                    res = rs.getString("cat_parent") + Category.separator + rs.getString("cat_name");
                }
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return res;
    }

    private Category getCategory(String c) {
        Connection connection = null;
        Category res = null;
        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbname);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.
            String sql = "SELECT * FROM category WHERE cat_name = '" + c + "'";
            System.err.println(sql);
            ResultSet rs = statement.executeQuery(sql);
            if (rs != null && rs.next()) {
                res = new Category(rs.getString("cat_parent") + Category.separator + rs.getString("cat_name"));
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return res;
    }

    public Page getPage(String name) {
        if (isPageNameValid(name)) {
            Connection connection = null;
            try {
                // create a database connection
                connection = DriverManager.getConnection("jdbc:sqlite:" + dbname);
                Statement statement = connection.createStatement();
                statement.setQueryTimeout(30);  // set timeout to 30 sec.

                String sql = "SELECT * FROM page WHERE page_name = '" + name + "'";
                System.err.println(sql);
                ResultSet rs = statement.executeQuery(sql);

                if (rs != null && rs.next()) {
                    //new Page(name, text, cat, tags, comment, version)
                    // TODO: obter tags da tabela page_tag
                    return new Page(rs.getString("page_name"), rs.getString("page_text"), rs.getString("cat_name"), "", rs.getString("page_comment"), new Version(rs.getString("page_version")));
                }
            } catch (SQLException e) {
                System.err.println(e.getMessage());
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    private boolean isPageNameValid(String name) {
        return (name != null && !name.equals(""));
    }

    private boolean isPageTextValid(String text) {
        return (text != null && !text.equals(""));
    }

    // TODO: verificar se e uma versao maior do que a do banco?
    private boolean isPageVersionValid(String version) {
        if (version == null || version.equals("")) {
            return false;
        }
        return Version.isValidVersion(version);
    }

    // permitir comentario vazio
    private boolean isPageCommentValid(String comment) {
        return (comment != null);
    }

    // permitir categoria vazia???
    private boolean isPageCategoryValid(String category) {
        return (category != null && !category.equals(""));
    }

    public ArrayList<String> getCategories() {
        ArrayList<String> list = new ArrayList<String>();
        Connection connection = null;
        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbname);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            String sql = "SELECT * FROM category";
            System.err.println(sql);
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                list.add(rs.getString("cat_parent") + Category.separator + rs.getString("cat_name"));
            }

        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public ArrayList<String> getCategoriesWithParent(String parent) {
        ArrayList<String> list = new ArrayList<String>();
        if (parent == null || parent.equals("")) {
            return null;
        } else {
            Connection connection = null;
            try {
                // create a database connection
                connection = DriverManager.getConnection("jdbc:sqlite:" + dbname);
                Statement statement = connection.createStatement();
                statement.setQueryTimeout(30);  // set timeout to 30 sec.

                String sql = "SELECT * FROM category WHERE cat_parent = '" + parent + "'";
                System.err.println(sql);
                ResultSet rs = statement.executeQuery(sql);

                while (rs.next()) {
                    list.add(rs.getString("cat_name"));
                }

            } catch (SQLException e) {
                System.err.println(e.getMessage());
                e.printStackTrace();
            } finally {
                try {
                    if (connection != null) {
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    public ArrayList<String> getPagesWithCategory(String category) {
        ArrayList<String> list = new ArrayList<String>();
        Connection connection = null;
        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbname);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            String sql = "SELECT * FROM page WHERE cat_name = '" + category + "'";
            System.err.println(sql);
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {
                list.add(rs.getString("page_name"));
            }

        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public void test() throws ClassNotFoundException {
        Connection connection = null;
        try {
            // create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:" + dbname);
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);  // set timeout to 30 sec.

            statement.executeUpdate("drop table if exists person");
            statement.executeUpdate("create table person (id integer, name string)");
            statement.executeUpdate("insert into person values(1, 'leo')");
            statement.executeUpdate("insert into person values(2, 'yui')");
            ResultSet rs = statement.executeQuery("select * from person");
            while (rs.next()) {
                // read the result set
                System.out.println("name = " + rs.getString("name"));
                System.out.println("id = " + rs.getInt("id"));
            }
        } catch (SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found
            System.err.println(e.getMessage());
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }
}
